﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class Suppliers : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection();
    
    SqlDataAdapter da;
    DataSet ds;
    SqlCommandBuilder cb;
    DataTable dt;
    DataView dv;

    protected void Page_Load(object sender, EventArgs e)
    {
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        con.ConnectionString = constr;
        FillSupplier();
        InitializeADObject_Supplier();
        LoadSupplierInfo();
        btnDelete.Enabled = false;
        btnUpdate.Enabled = false;
    }

    void FillSupplier()
    {
        SqlConnection con = new SqlConnection();
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        con.ConnectionString = constr;

        SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Production.Suppliers", con.ConnectionString);
        DataTable dt = new DataTable("Production.Suppliers");
        da.Fill(dt);

        dgvSupplier.AutoGenerateSelectButton = true;
        dgvSupplier.AllowPaging = true;
        dgvSupplier.PageSize = 5;
        dgvSupplier.DataSource = dt;
        dgvSupplier.DataBind();

    }

    void InitializeADObject_Supplier()
    {
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "Production.Suppliers_load";

        da = new SqlDataAdapter(cmd);
        cb = new SqlCommandBuilder(da);

        ds = new DataSet();
        da.FillSchema(ds, SchemaType.Mapped);
        da.Fill(ds, "Production.Suppliers");
    }

    void LoadSupplierInfo()
    {
        dt = ds.Tables["Production.Suppliers"];
        dv = new DataView(dt);
        dgvSupplier.DataSource = dt;
        dgvSupplier.DataBind();
    }

    void ClearForm()
    {
        txtID.Text = "";
        txtCompanyName.Text = "";
        txtContactName.Text = "";
        txtContactTitle.Text = "";
        txtAddress.Text = "";
        txtCity.Text = "";
        txtRegion.Text = "";
        txtPostalCode.Text = "";
        DDLCountry.SelectedIndex = -1;
        txtPhone.Text = "";
        txtFax.Text = "";
        //btnUpdate.Enabled = false;
        //btnDelete.Enabled = false;
        //btnAdd.Enabled = true;

    }

    void RefreshSupplier()
    {
        InitializeADObject_Supplier();
        LoadSupplierInfo();
        ClearForm();
        
    }

    void FilterbyCountry()
    {
      
        if (DDLFilter.Text.Length != 0)
        {
            dv = new DataView(dt);
            dv.RowFilter = string.Format("country like '%{0}%'", DDLFilter.Text);
            dgvSupplier.DataSource = dv;
            dgvSupplier.DataBind();
        }

    }

    void SearchbyName()
    {
       
        dv = new DataView(dt);
        dv.RowFilter = string.Format("companyname like '%{0}%' ", txtSearch.Text);
        dgvSupplier.DataSource = dv;
        dgvSupplier.DataBind();
    }


    protected void dgvSupplier_SelectedIndexChanged(object sender, EventArgs e)
    {

        GridViewRow r = dgvSupplier.SelectedRow;
        txtID.Text = r.Cells[1].Text;
        txtCompanyName.Text = Server.HtmlDecode(r.Cells[2].Text);
        txtContactName.Text = Server.HtmlDecode(r.Cells[3].Text);
        txtContactTitle.Text = Server.HtmlDecode(r.Cells[4].Text);
        txtAddress.Text = Server.HtmlDecode(r.Cells[5].Text);
        txtCity.Text = Server.HtmlDecode(r.Cells[6].Text);
        txtRegion.Text = Server.HtmlDecode(r.Cells[7].Text);
        txtPostalCode.Text = Server.HtmlDecode(r.Cells[8].Text);
        DDLCountry.SelectedItem.Text = r.Cells[9].Text;
        txtPhone.Text = Server.HtmlDecode(r.Cells[10].Text);
        txtFax.Text = Server.HtmlDecode(r.Cells[11].Text);
        btnUpdate.Enabled = true;
        btnDelete.Enabled = true;
        btnAdd.Enabled = false;


       
    }

    protected void dgvSupplier_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        dgvSupplier.PageIndex = e.NewPageIndex;
        dgvSupplier.DataBind();
    }

    protected void btnAdd_Click(object sender, EventArgs e)

    {
        try 
	{	        
		con.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "Production.Suppliers_add";
        SqlParameter param = new SqlParameter("@companyname", SqlDbType.NVarChar, 40);
        param.Value = (txtCompanyName.Text);
        cmd.Parameters.Add(param);

        param = new SqlParameter("@contactname", SqlDbType.NVarChar, 30);
        param.Value = (txtContactName.Text);
        cmd.Parameters.Add(param);

        param = new SqlParameter("@contacttitle", SqlDbType.NVarChar, 30);
        param.Value = (txtContactTitle.Text);
        cmd.Parameters.Add(param);

        param = new SqlParameter("@address", SqlDbType.NVarChar, 60);
        param.Value = (txtAddress.Text);
        cmd.Parameters.Add(param);

        param = new SqlParameter("@city", SqlDbType.NVarChar, 15);
        param.Value = (txtCity.Text);
        cmd.Parameters.Add(param);

        param = new SqlParameter("@region", SqlDbType.NVarChar, 15);
        param.Value = (txtRegion.Text);
        cmd.Parameters.Add(param);

        param = new SqlParameter("@postalcode", SqlDbType.NVarChar, 10);
        param.Value = (txtPostalCode.Text);
        cmd.Parameters.Add(param);

        param = new SqlParameter("@country", SqlDbType.NVarChar, 15);
        param.Value = (DDLCountry.Text);
        cmd.Parameters.Add(param);

        param = new SqlParameter("@phone", SqlDbType.NVarChar, 24);
        param.Value = (txtPhone.Text);
        cmd.Parameters.Add(param);

        param = new SqlParameter("@fax", SqlDbType.NVarChar, 24);
        param.Value = (txtFax.Text);
        cmd.Parameters.Add(param);

        

        cmd.ExecuteNonQuery();
        Response.Write("<script language='JavaScript'> alert('ADD SUCCESSFULLY!'); </script>");
      con.Close();
	}
	catch (Exception ex)
	{

        Response.Write("<script language='JavaScript'> alert('CANNOT ADD') ;</script>");
	}


    
        RefreshSupplier();
    }

    protected void btnNew_Click(object sender, EventArgs e)
    {
        RefreshSupplier();
        btnAdd.Enabled = true;
    }

    protected void btnDelete_Click(object sender, EventArgs e)
    {
        con.Open();

        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "Production.Suppliers_delete";


            SqlParameter param = new SqlParameter("@supplierid", SqlDbType.Int);

            //SqlParameter param = new SqlParameter("@supplierID", SqlDbType.Int);

            param.Value = int.Parse(txtID.Text);
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
            Response.Write("<script language='JavaScript'> alert('DELETE SUCCESSFULLY!'); </script>");

        }
        catch (Exception ex)
        {

           Response.Write("<script language='JavaScript'> alert('SORRY! YOU CANNOT DELETE THIS ROW'); </script>");
        }

     

        con.Close();

        RefreshSupplier();
    }

    protected void btnUpdate_Click(object sender, EventArgs e)
    {

        con.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "Production.Suppliers_update";
        SqlParameter param = new SqlParameter("@companyname", SqlDbType.NVarChar, 40);
        param.Value = (txtCompanyName.Text);
        cmd.Parameters.Add(param);

        param = new SqlParameter("@contactname", SqlDbType.NVarChar, 30);
        param.Value = (txtContactName.Text);
        cmd.Parameters.Add(param);

        param = new SqlParameter("@contacttitle", SqlDbType.NVarChar, 30);
        param.Value = (txtContactTitle.Text);
        cmd.Parameters.Add(param);

        param = new SqlParameter("@address", SqlDbType.NVarChar, 60);
        param.Value = (txtAddress.Text);
        cmd.Parameters.Add(param);

        param = new SqlParameter("@city", SqlDbType.NVarChar, 15);
        param.Value = (txtCity.Text);
        cmd.Parameters.Add(param);

        param = new SqlParameter("@region", SqlDbType.NVarChar, 15);
        param.Value = (txtRegion.Text);
        cmd.Parameters.Add(param);

        param = new SqlParameter("@postalcode", SqlDbType.NVarChar, 10);
        param.Value = (txtPostalCode.Text);
        cmd.Parameters.Add(param);

        param = new SqlParameter("@country", SqlDbType.NVarChar, 15);
        param.Value = (DDLCountry.Text);
        cmd.Parameters.Add(param);

        param = new SqlParameter("@phone", SqlDbType.NVarChar, 24);
        param.Value = (txtPhone.Text);
        cmd.Parameters.Add(param);

        param = new SqlParameter("@fax", SqlDbType.NVarChar, 24);
        param.Value = (txtFax.Text);
        cmd.Parameters.Add(param);


        param = new SqlParameter("@supplierid", SqlDbType.Int);
        param.Value = int.Parse(txtID.Text);
        cmd.Parameters.Add(param);

  

        cmd.ExecuteNonQuery();  
        Response.Write("<script language='JavaScript'> alert('UPDATE SUCCESSFULLY!'); </script>");
        con.Close();
        RefreshSupplier();
        
    }

    protected void btnFilter_Click(object sender, EventArgs e)
    {
        FilterbyCountry();
    }



    protected void btnSearch_Click(object sender, EventArgs e)
    {
        SearchbyName();

    }
    protected void CustomValidator1_ServerValidate(object source, ServerValidateEventArgs args)
    {

    }
}
